IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_InspectionTypesByWeekAndInspector')
	BEGIN
		DROP  Procedure  up_Report_InspectionTypesByWeekAndInspector
	END

GO

CREATE Procedure [dbo].[up_Report_InspectionTypesByWeekAndInspector]
	@ProjectId int = null
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SET @ProjectId = 6
END



select
	C.Name as Inspector_Name,
	T.Name as InspectionType_Name,
	R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk as InspectionType,
	P.InspectorFk as Inspector,
	1 as numberInspection,
	convert(nvarchar,dbo.fn_GetFirstDateofWeek(P.AcquisitionDate),101) as FirstWeekDate,
	convert(nvarchar,dbo.fn_GetLastDateofWeek(P.AcquisitionDate),101) as LastWeekDate,
	DATEPART(wk, P.AcquisitionDate) AS weekNumber
from tb_Vendor_InspVisit_XmissionStructures_Poles P
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Results R ON P.Vendor_InspVisit_XMissionStructure_PoleId = R.Vendor_InspVisit_XMissionStructure_PoleFk
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Inspection_Type T ON R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk = T.Vendor_InspVisit_XmissionStructures_Inspection_TypeId
--INNER JOIN tb_Utility_Contacts C ON P.InspectorFk = C.Utility_ContactId
INNER JOIN tb_Vendor_Contacts C ON P.Vendor_ContactFK = C.Vendor_ContactId
WHERE 
	Vendor_ProjectFK = @ProjectId
order by DATEPART(wk, P.AcquisitionDate)

GO

GRANT EXEC ON up_Report_InspectionTypesByWeekAndInspector TO PPIReports

GO



